const Router = require('koa-router');
const router = new Router();
const tool = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");
// 销售排行
router.post('/sale/xsph', async ctx => {
    try {
        let body = ctx.request.body
        let tenantId = ctx.user.tenantId;
        let excel = body.excel ? 1 : 0;
        let search = body.formData;
        let beginDate = search.beginDate ? search.beginDate : '';
        let endDate = search.endDate ? search.endDate : '';
        let buId = search.salesId ? search.buId : 0;
        let goodCatCode = await tool.getGoodCode(ctx)
        let invName = search.invName?search.invName:'';
        let gpType = search.gpType ? search.gpType : '按商品';
        if(!beginDate) return ctx.err('请选择开始日期')
        if(!endDate) return ctx.err('请选择结束日期')

        let sql = `
         select a.tenantId, a.buId,b.invId,c.spec,c.unit1,b.calNum*b.qty qty,isnull(b.amount,0) amount from invoice a inner join 
             dbo.v_main_info  b on a.tenantId=b.tenantId and a.id=b.iid and a.billState='accounted' and a.billDate between '${beginDate}' and '${endDate}' and a.tenantId=${tenantId} 
         and (a.buId=${buId} or ${buId}=0) 
             inner join v_goods c on a.tenantId=c.tenantId and b.invId=c.id and a.billType='SALE'  
         `
        if(goodCatCode) {
            sql += ` and c.goodCatCode like '${goodCatCode}%'  `
        }
        if(invName) {
            sql += ` and c.name like '%${invName}%' `
        }
        let columns = []
        if(gpType === '按客户') {
            sql = ` select a.buId,b.name buName,sum(a.amount) amount from (${sql}) a 
                inner join contact b on a.tenantId=b.tenantId and a.buId=b.id group by a.buId,b.name order by sum(a.qty) desc `
            columns = [
                {type: 'seq', width: 50},
                {field: 'buName', title: '客户名称', width: 220,filter: true,sortable: true,visible: true},
                {field: 'amount', title: '销售额', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
            ]
        }
        if(gpType === '按商品') {
            sql = ` select a.invId,b.name invName,b.spec,b.unit1,sum(a.amount) amount,round(sum(a.qty)/b.spec,2) qty from (${sql}) a 
                inner join v_goods b on a.tenantId=b.tenantId and a.invId=b.id group by a.invId,b.name,b.spec,b.unit1 order by sum(a.qty) desc `
            columns = [
                {type: 'seq', width: 50},
                {field: 'invName', title: '商品名称', width: 220,filter: true,sortable: true,visible: true},
                {field: 'spec', title: '规格', width: 80,filter: true,sortable: true,align: 'center',visible: true},
                {field: 'unit1', title: '单位', width: 80,filter: true,sortable: true,align: 'center',visible: true},
                {field: 'qty', title: '销量', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'amount', title: '销售额', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
            ]
        }
        if(gpType === '按客户商品') {
            sql = ` select a.buId,c.name buName, a.invId,b.name invName,b.spec,b.unit1,sum(a.amount) amount,round(sum(a.qty)/b.spec,2) qty from (${sql}) a 
                inner join v_goods b on a.tenantId=b.tenantId and a.invId=b.id 
                inner join contact c on a.tenantId=c.tenantId and a.buId=c.id 
                group by a.buId,a.invId,c.name,b.name,b.spec,b.unit1 order by sum(a.qty) desc `
            columns = [
                {type: 'seq', width: 50},
                {field: 'buName', title: '客户名称', width: 220,filter: true,sortable: true,visible: true},
                {field: 'invName', title: '商品名称', width: 220,filter: true,sortable: true,visible: true},
                {field: 'spec', title: '规格', width: 80,filter: true,sortable: true,align: 'center',visible: true},
                {field: 'unit1', title: '单位', width: 80,filter: true,sortable: true,align: 'center',visible: true},
                {field: 'qty', title: '销量', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
                {field: 'amount', title: '销售额', width: 100, align:'right',sortable: true, formatter: 'formatMoney',sum: true,visible: true},
            ]
        }
        let list = await db.query(sql, {type: QueryTypes.SELECT})
        columns.map(row => {
            row.excelOut = true
        })
        if(excel) {
            return tool.doExcelOut(ctx, list, null, columns)
        }
        else {
            return ctx.suc('', {
                total: 0,
                list,
                columns
            })
        }

    }
    catch (e) {
        return ctx.err(e)
    }

})


module.exports = router;
